package com.babel.util.util;

import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

/**
 * Created by Thinkpad on 2015/4/22.
 */
public class ExcelUtil{
    /**
     *
     * @param data 数据
     * @param titles 标题
     * @return
     */
        public static HSSFWorkbook getWorkbook(List<LinkedHashMap<String,Object>> data,String [] titles){

            HSSFWorkbook wb = new HSSFWorkbook();
            // 另一个字体样式
            HSSFFont columnHeadFont = wb.createFont();
            columnHeadFont.setFontName("宋体");
            columnHeadFont.setFontHeightInPoints((short) 10);
            columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            // 列头的样式
            HSSFCellStyle columnHeadStyle = wb.createCellStyle();
            columnHeadStyle.setFont(columnHeadFont);
            columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
            columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
            columnHeadStyle.setLocked(true);
            columnHeadStyle.setWrapText(true);
            columnHeadStyle.setLeftBorderColor(HSSFColor.BLACK.index);// 左边框的颜色
            columnHeadStyle.setBorderLeft((short) 1);// 边框的大小
            columnHeadStyle.setRightBorderColor(HSSFColor.BLACK.index);// 右边框的颜色
            columnHeadStyle.setBorderRight((short) 1);// 边框的大小
            columnHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体
            columnHeadStyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色
            // 设置单元格的背景颜色（单元格的样式会覆盖列或行的样式）
            columnHeadStyle.setFillForegroundColor(HSSFColor.WHITE.index);


            HSSFFont font = wb.createFont();
            font.setFontName("宋体");
            font.setFontHeightInPoints((short) 10);
            // 普通单元格样式
            HSSFCellStyle style = wb.createCellStyle();
            style.setFont(font);
            style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居中
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中
            style.setWrapText(false);
            style.setLeftBorderColor(HSSFColor.BLACK.index);
            style.setBorderLeft((short) 1);
            style.setRightBorderColor(HSSFColor.BLACK.index);
            style.setBorderRight((short) 1);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体
            style.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色．
            style.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色．
            try {
                HSSFSheet sheet = wb.createSheet("sheet");
                int rowNumber=0;
                //写出标题
                if(titles!=null&&titles.length!=0){
                    HSSFRow titleRow = sheet.createRow(rowNumber);
                    titleRow.setHeight((short)500);
                    for(int i=0;i<titles.length;i++){
                        HSSFCell titleCell = titleRow.createCell(i);
                        HSSFRichTextString content=new HSSFRichTextString(titles[i]);
                        titleCell.setCellValue(content);
                        //设置列宽
                        sheet.setColumnWidth(i,5000);
                       // sheet.setDefaultColumnStyle(i,sheetStyle);
                        // 设置列的样式
                        titleCell.setCellStyle(columnHeadStyle);
                    }
                    rowNumber++;
                }
                //写出内容
                if(data!=null&&data.size()!=0){
                    for(int i=0;i<data.size();i++){
                        HSSFRow contentRow = sheet.createRow(rowNumber);
                        contentRow.setHeight((short)400);
                        Map<String, Object> dataRow = data.get(i);
                        int j=0;
                        for(String key:dataRow.keySet()){
                            HSSFCell contentCell = contentRow.createCell(j);
                            Object value=dataRow.get(key);
                            value=value==null?"":dataRow.get(key);
                            HSSFRichTextString content=new HSSFRichTextString(value.toString());
                            contentCell.setCellValue(content);
                            contentCell.setCellStyle(style);
                            j++;
                        }
                        rowNumber++;
                    }
                }
            }catch (Exception e){
                e.printStackTrace();
            }
            return wb;
        }
}
